<?php
namespace Operationtest\Model;

use Zend\Db\Adapter\Adapter;
use Zend\Db\ResultSet\ResultSet;
use Zend\Db\TableGateway\AbstractTableGateway;

class SalerowTable extends AbstractTableGateway
{
    protected $table ='saleRow';
    
    public function __construct(Adapter $adapter)
    {
        $this->adapter = $adapter;
        $this->resultSetPrototype = new ResultSet();
        $this->resultSetPrototype->setArrayObjectPrototype(new Salerow());
        $this->initialize();
    }
    
    public function getFamilies() {
        $rows = array();
        $sql = "SELECT 
                    idFamily,
                    name
                FROM Family
                ORDER BY name ASC";
        
        $result = $this->adapter->query($sql)->execute();
        for($x=0;$x<count($result);$x++):
            $rows[$x] = $result->current();
        endfor;

        return $rows;
    }
    
    public function getTbNbArticle($where) {
        $sql = "SELECT 
                    SUM(saleRow.quantity) AS nbArticle
                FROM ".$this->table."
                JOIN saleHead
                    ON (saleRow.idsaleHead = saleHead.idSaleHead)
                WHERE ".$where." AND saleHead.canceled = 0 AND (saleHead.workerMeal = 0 OR saleHead.workerMeal is NULL)";
        
        $result = $this->adapter->query($sql)->execute();
        $return = $result->current();
        
        return $return;
    }
    
    public function getNbArticle($where, $groupby="") {
        $rows = array();
        $sql = "SELECT 
                    SUM(saleRow.quantity) AS nbArticle,
                    saleHead.idStore,
                    saleHead.orderDate
                FROM ".$this->table."
                JOIN saleHead
                    ON (saleRow.idsaleHead = saleHead.idSaleHead)
                WHERE ".$where." AND saleHead.canceled = 0 AND (saleHead.workerMeal = 0 OR saleHead.workerMeal is NULL)
                ".$groupby;
        
        $result = $this->adapter->query($sql)->execute();
        for($x=0;$x<count($result);$x++):
            $rows[$x] = $result->current();
        endfor;

        return $rows;
    }
    
    public function getNbMarcheArticle($where) {
        $rows = array();
        $sql = "SELECT 
                    SUM(saleRow.quantity) AS nbArticle,
                    idMarche
                FROM ".$this->table."
                JOIN saleHead
                    ON (saleRow.idsaleHead = saleHead.idSaleHead)
                JOIN MarcheStore
                    ON (MarcheStore.idStore = saleHead.idStore)
                WHERE ".$where." AND saleHead.canceled = 0 AND (saleHead.workerMeal = 0 OR saleHead.workerMeal is NULL)
                GROUP BY idMarche";
        
        $result = $this->adapter->query($sql)->execute();
        for($x=0;$x<count($result);$x++):
            $rows[$x] = $result->current();
        endfor;

        return $rows;
    }
    
    public function getRapportProduit($where, $groupby="") {
        $rows = array();
        $sql = "SELECT 
                    Family.name AS fname,
                    Article.libelleCaisse AS alibelle,
                    SUM(saleRow.quantity) AS quantite,
                    SUM(saleRow.totalPriceHt) AS caht,
                    SUM(saleRow.totalPriceTtc) AS cattc,
                    saleHead.canceled,
                    saleHead.orderDate
                FROM ".$this->table."
                JOIN saleHead
                    ON (saleRow.idsaleHead = saleHead.idSaleHead)
                JOIN Article
                    ON (saleRow.idArticle = Article.idArticle)
                JOIN Family
                    ON (Article.idFamily = Family.idFamily)
                WHERE ".$where." AND saleHead.canceled = 0 AND (saleHead.workerMeal = 0 OR saleHead.workerMeal is NULL)
                ".$groupby." ORDER BY Family.name ASC, Article.libelleCaisse ASC";
        //echo $sql;
        $result = $this->adapter->query($sql)->execute();
        for($x=0;$x<count($result);$x++):
            $rows[$x] = $result->current();
        endfor;

        return $rows;
    }
}
